var utility = require('system/utility'),
	tedious = require('tedious'),
	DataSet = require('./dataset'),
	TYPES = tedious.TYPES,
	servers = {
		dev: { server: '10.6.131.61', port: 1433, userName: 'developer', password: 'developer' },
		qa: { server: '10.6.131.60', port: 1433, userName: 'developer', password: 'developer' },
		prod: { server: '10.6.131.178', port: 1433, userName: 'developer', password: 'developer' }
	};

function log() {
	utility.log("tedious", arguments);
}

function openDB(serverCredentials, callback) {
	var connectionObject = {
		handler: {
			error: function (error) {
				log("DB error: " + error);
			},
			message: function (msg) {
				log("DB message: " + msg);
			},
			connect: function (err) {
				log("tedious connected: " + serverCredentials.server);
				if (callback) {
					callback(err);
				} else {
					log("NO SQL CALLBACK!!!")
				}
			}
		}
	};
	utility.extend(connectionObject, serverCredentials);
	//log("instantiating connection")

	var db = new tedious.Connection(connectionObject);
	try {
		db.on('connect', callback);
	} catch (e) {
		callback("Oops: database connection failed: " + e.message);
	}
	return db;
}

function doQuery(server, query, options, callback) {
	var results = {
		data: {
			success: false,
			total: 0,
			rows: []
		}
	}, db, svr = servers[server];
	var sql, rows = [], str = "", ds, rowCount = 0, i, p, row;
	function close() {
		if (db) {
			db.close();
			db = null;
		}
	}
	if (!svr) {
		log("No server found for query: " + server);
	} else {
		//log("Server[" + server + "] SQL: " + query);
		db = openDB(svr, function (error) {
			if (error != null) {
				results.data.errors = ['db connect error: ' + error];
				close();
				callback(results);
			} else {
				try {
					ds = new DataSet.DataSet();
					sql = new tedious.Request(query, function (err, rowCount) {
						try {
							results.data.total = rowCount;
							if (err) {
								log("ERROR." + server + " / " + query + ": ", err);
								results.errors = [err];
							} else {
								results.data.success = true;
								//log("DONE(", rowCount, ") [",query,"]");
							}
							close();
						} catch (e) {
							log("sql.done[" + rowCount + "].exception: " + e.toString());
						}
						try {
							callback(results);
						} catch (e) {
							log("sql.done.callback.exception: " + e.toString());
						}
					});
					if (options && options.params) {
						for (i = 0; i < options.params.length; i++) {
							p = options.params[i];
							if (p.name && p.value && p.type) {
								sql.addParameter(p.name, TYPES[p.type], p.value);
							}
						}
					}
					sql.on('row', function (r) {
						try {
							row = new DataRow(r);
							//log("Row: ",row);
							rowCount++;
							results.data.rows.push(row);
						} catch (e) {
							log("sql.row[" + rowCount + "].exception: " + e.toString());
						}
					});
					//log("executing query: " +  query);
					db.execSql(sql);
				} catch (e) {
					log("(TEDIOUS) SQL exception: ", e)
				}
			}
		});
	}
}

function DataRow(tdsRecord) {
	var me = this;
	me.tdsRecord = tdsRecord;
	utility.extend(me, {
		eachColumn: function (callback) {
			var c, cc, v;
			for (c = 0; c < me.tdsRecord.length; c++) {
				cc = me.tdsRecord[c];
				try {
					v = cc.value;
				} catch (e) {
					v = "";
					log("oops (tedious.DataRow.each.getValue): ", cc.metadata.colName, ", ", e);
				}
				try {
					callback(cc.metadata.colName, v);
				} catch (e) {
					log("oops (tedious.DataRow.each.callback): ", cc.metadata.colName, ", ", e);
				}
			}
		},
		asHtmlHeader: function () {
			var str = "";
			me.eachColumn(function (c, v) { str += "<td>" + c + "</td>"; });
			return str.length ? "<tr>" + str + "</tr>\n" : "";
		},
		asHTML: function () {
			var str = "";
			me.eachColumn(function (c, v) { str += "<td>" + v + "</td>"; });
			return str.length ? "<tr>" + str + "</tr>\n" : "";
		},
		asObject: function () {
			var obj = {};
			me.eachColumn(function (c, v) { obj[c] = v; });
			return obj;
		}
	});
}

exports.DataRow = DataRow;
exports.query = doQuery;
//exports.close = closeDB;